package com.bankLogisticsSys.service;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import com.bankLogisticsSys.model.Business;
import com.bankLogisticsSys.model.Networkinfo;
import com.bankLogisticsSys.model.Packageinfo;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class PackageService {
	public boolean insert(Packageinfo pk){
		return pk.save();
	}
	
	public List<Record> getPackageList(String name,int page, int rows, String sort, String order){
		int start = ((page - 1) * rows);
		int end = (page * rows);
		List<Object> para = new ArrayList<>();
		String condition = getCondition(name,sort, order, para);
		para.add(start);
		para.add(end);
		String sql = "SELECT p.id,if(p.type=1,'大包','小包')type,p.packageCode,p.department,p.businessID business,if(p.state=1,'弃用','正常')state,"
				+ "(select network from networkinfo where p.location = id)location,n.network,"
				+ "(select business from business where p.businessID = businessID)business, "
				+ "(select bankType from banktype where id = p.bank)bank "
				+ "FROM packageinfo as p "
				+ "join networkinfo as n "
				+ "ON p.networkid = n.id " + condition
				+ " limit ?,?";
		System.out.println(sql);
		List<Record> ui = Db.find(sql, para.toArray());
		return ui;
	}
	
	
	public String getCondition(String name,String sort, String order,List<Object> para){
		String condition="";
		if(StringUtils.isNotBlank(name)){
			condition += " and n.network like ?";
			para.add("%"+name+"%");
		}
		condition += " order by " + sort + " " + order;
		return condition;
	}
	public Packageinfo getPackage(int packageid){
		List<Packageinfo> list = Packageinfo.dao.find("select * from packageinfo where id = ?", packageid);
		if (list.size() == 0)
			return new Packageinfo();
		else
			return list.get(0);

	}
	public long getTotalCount(String name){
		List<Object> para = new ArrayList<>();
		String condition = getCondition(name,"p.id","asc", para);
		String sql = "SELECT count(*) "
				+ "FROM packageinfo as p "
				+ "join networkinfo as n "
				+ "ON p.networkid = n.id " + condition;
		return Db.queryLong(sql,para.toArray());
	}
	
	public List<Networkinfo> getNetworkList(){
		List<Networkinfo> ni = Networkinfo.dao.find("select id, network from networkinfo where type in(1)");
		return ni;
	}
	
	
	public boolean delete(Packageinfo pk){
		Packageinfo pk2 = Packageinfo.dao.findFirst("select * from packageinfo where id = ?", pk.getId());
		if(Db.queryLong("select count(*) from expressrecord where id = ?", pk2.getId()) == 0){;
			return pk.delete();
		}
		return false;
	}
	
	public boolean edite(Packageinfo pk){
		return pk.update();
	}
	public boolean isExistPackageCode(String packageCode){
		return (Packageinfo.dao.findFirst("select id from packageinfo where packagecode = ?", packageCode)!=null);
	}

	public List<Business> getBusinessList() {
		List<Business> bi = Business.dao.find("select businessID, business from business");
		return bi;
	}
}
